package net.tngou.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import net.tngou.action.BaseAction;

public class ExcelUtil {
	private static final Logger log= LoggerFactory.getLogger(BaseAction.class);
	private final static String XLS = "xls";  
    private final static String XLSX = "xlsx"; 
	//工作表
	public class Sheet{
		private String title;//标题
		private List<Object[]>  cells;//单元格数据
		public String getTitle() {
			return title;
		}
		public void setTitle(String title) {
			this.title = title;
		}
		public  List<Object[]> getCells() {
			return cells;
		}
		public void setCells(List<Object[]> cells) {
			this.cells = cells;
		}
		
	}
	
	public static List<Sheet> read(String excelname) {		
		File excelfile = new File(excelname);
		return read(excelfile );
	}
	public static List<Sheet> read(File excelfile) {
		 List<Sheet> ls = new ArrayList<Sheet>();  
		if(!_checkFile(excelfile))
		{
			return null;
		}
		Workbook workbook=_getWorkBook(excelfile);
		if(workbook==null) return null;
		for (org.apache.poi.ss.usermodel.Sheet sheet : workbook) {
			Sheet my =new  ExcelUtil().new Sheet();
			String name=sheet.getSheetName();
			List<Object[]> rows = new ArrayList<Object[]>();
			my.setTitle(name);
			//获得当前sheet的开始行  
            int firstRowNum  = sheet.getFirstRowNum();  
            //获得当前sheet的结束行  
            int lastRowNum = sheet.getLastRowNum();  
            //循环除了第一行的所有行  
            for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){  
                //获得当前行  
                Row row = sheet.getRow(rowNum);  
                if(row == null){  
                    continue;  
                }  
                //获得当前行的开始列  
                int firstCellNum = row.getFirstCellNum();  
                //获得当前行的列数  
                int lastCellNum = row.getPhysicalNumberOfCells(); 
                String[] cells = new String[row.getPhysicalNumberOfCells()]; 
                //循环当前行  
                for(int cellNum = firstCellNum; cellNum <lastCellNum;cellNum++){  
                    Cell cell = row.getCell(cellNum);     
                    if(cell!=null)
                    cells[cellNum] = cell.getStringCellValue();  
                }  
                rows.add(cells);  
                my.setCells(rows);
            }  
            if(my.getCells()!=null)
            ls.add(my);
		}
		try {
			workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return ls;
		
	}
	
	
	/**
	 * 校验文件
	 * @param file
	 * @return
	 */
	private static boolean _checkFile(File file)
	{
		 //判断文件是否存在  
        if(!file.isFile()){  
            log.info("文件{}不存在！",file.getPath());  
            return false;
        }
      //获得文件名  
        String fileName = file.getName();  
        //判断文件是否是excel文件  
        if(!fileName.endsWith(XLS) && !fileName.endsWith(XLSX)){  
            log.info( "{}不是excel文件",fileName);  
            return false;
        }        
        return true;
        
	}
	
	public static Workbook _getWorkBook(File file)
	{
		//获得文件名  
        String fileName = file.getName();  
        //创建Workbook工作薄对象，表示整个excel  
        Workbook workbook = null;  
        try {  
            //获取excel文件的io流  
            InputStream is = new FileInputStream(file);  
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象  
            if(fileName.endsWith(XLS)){  
                //2003  
                workbook = new HSSFWorkbook(is);  
            }else if(fileName.endsWith(XLSX)){  
                //2007  
                workbook = new XSSFWorkbook(is);  
            }  
            is.close();
        } catch (IOException e) {  
             
        }  
        return workbook;  	
	}
	
	

}
